package com.tnl.app.dao;

import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import com.tnl.app.entity.EmployeePerson;
import com.tnl.app.entity.JobDesc;
import com.tnl.app.entity.Employee;
import com.tnl.base.BaseDAO;

@Repository
public class EmployeeDAO {

	static Logger logger = LoggerFactory.getLogger(EmployeeDAO.class);

	private static final String SQL_GET_OPENID_STATUS = "select t1.*,t2.* from tb_person as t1, tb_employee t2  "
			+ "where t1.id = t2.personID and t2.openid = ? ";

	public Employee getByOpenId(String openId) {
		return BaseDAO.queryForObject(Employee.class, SQL_GET_OPENID_STATUS,
				openId);
	}

	protected static final String UPDATE_EMP_DOMAIN = "update tb_employee "
			+ "set state='completed',domainId=?, domainName =?,majorDomainId1=?,majorDomainId2=?,majorDomainId3=? where personId =?";

	public void updateDomain(Employee emp) {
		int result = BaseDAO.execute(UPDATE_EMP_DOMAIN, emp.getDomainId(),
				emp.getDomainName(),emp.getMajorDomainId1(),emp.getMajorDomainId2(),emp.getMajorDomainId3(),emp.getPersonId());
		logger.info("result="+result);
	}
	protected static final String SQL_SAVE_EMP_DOMAIN = "insert into tb_employee(openid,personId,domainId, domainName,majorDomainId1,"
			+ "majorDomainId2,majorDomainId3) " + "values(?,?,?,?,?,?,?)";

	public int saveDomain(Employee emp){
		if (emp == null) {
			return -1;
		}
		Long id =BaseDAO.save(SQL_SAVE_EMP_DOMAIN, emp.getOpenid(),emp.getPersonId(),emp.getDomainId(),emp.getDomainName(),
				emp.getMajorDomainId1(),emp.getMajorDomainId2(),emp.getMajorDomainId3());
		if (id == null) {
			return -1;
		}
		return id.intValue();
	}
	
	protected static final String UPDATE_EMP_EXPECT_POSI="update tb_employee set state='completed',expectCompany1=?,expectCompanyName1=?,"
			+ "expectCompany2=?,expectCompanyName2=?,expectCompany3=?,expectCompanyName3=?,expectJob=?,expectJobName=? where personId =?";
	public int updateDreamPosi(Employee emp){
		int result=BaseDAO.execute(UPDATE_EMP_EXPECT_POSI, emp.getExpectCompany1(),emp.getExpectCompanyName1(),
				emp.getExpectCompany2(),emp.getExpectCompanyName2(),emp.getExpectCompany3(),emp.getExpectCompanyName3(),
				emp.getExpectJob(), emp.getExpectJobName(), emp.getPersonId());
		return result;
	}
	
	protected static final String UPDATE_EMP_COMMENT="update tb_employee set comment=?  where personId =?";
	public int updateBaseComment(Employee emp){
		return BaseDAO.execute(UPDATE_EMP_COMMENT, emp.getComment(),emp.getPersonId());
	}
	
	protected static final String SQL_GET_PHONE = "select * from tb_employee where phone=? and openid=?";

	public Employee getEmpByPhone(String phone,String openid) {
		return BaseDAO.queryForObject(Employee.class, SQL_GET_PHONE, phone,openid);
	}
	
	protected static final String SQL_GET_ID = "select * from tb_employee where personid=?";
	public Employee getEmpById(int id){
		return BaseDAO.queryForObject(Employee.class, SQL_GET_ID, id);
	}
	
	protected static final String SQL_GET_EMP_PHONE = "select * from tb_employee where phone=?";

	public Employee getEmpByPhone(String phone) {
		return BaseDAO.queryForObject(Employee.class, SQL_GET_EMP_PHONE, phone);
	}

	protected static final String SQL_GET_EMP_UNIONID = 
			"select b.* "
			+ " from tb_employee b  "
			+ " where b.openid =? order by empid desc LIMIT 0,1";
	public Employee getEmpByUnionid(String unionid){
		return BaseDAO.queryForObject(Employee.class, SQL_GET_EMP_UNIONID, unionid);
	}
	
	protected static final String SQL_SAVE_SECURECODE = "insert into tb_employee(openid, phone, secureCode,expiredTime,"
			+ "state,active ,personId) " + "values(?,?,?,?,?,?,?)";

	public int saveSecureCode(Employee emp) {
		if (emp == null) {
			return -1;
		}
		Long id = BaseDAO.save(SQL_SAVE_SECURECODE, emp.getOpenid(),
				emp.getPhone(), emp.getSecureCode(), emp.getExpiredTime(),
				emp.getState(), emp.getActive(), emp.getPersonId());
		if (id == null) {
			return -1;
		}

		return id.intValue();
	}
	
	  protected static final String SQL_BINDED_PHONE = "update tb_employee set state=?,active = ?"
			  + " where phone =?";

	  public long bindedPhone(Employee emp) {
	    int result = BaseDAO.execute(SQL_BINDED_PHONE, Employee.State.binded.toString(),
	                                 emp.getActive(), emp.getPhone());
	    return result;
	  }
	  

	  private static final String SQL_GET_EMP_ID = "select t1.*, t2.* from tb_person as t1, tb_employee t2 where t1.id = t2.personID and t2.empid= ? ";
	  public Employee getByEmpId(int employeeid){
			 return BaseDAO.queryForObject(Employee.class, SQL_GET_EMP_ID, employeeid);
	  }
	  
	  //获取职位相关的候选人信息，
	  //1 person 中job相同的，公司 TODO： 单纯的猎头的推荐人现在还不能查询出来。person表单独不能匹配domain and expect job
	  private static final String SEARCH_EMP_JOB = "select * from ("
	  		+ "select t1.* from tb_person as t1, " 
	  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
	   + " and t1.companyId <> ? and  t1.roleId in(?,?) " 
	      + "and t2.domainId in (?,?,?) "
	      + " union "
	      + "select t1.* from tb_person as t1, " 
	  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
	   + " and  ( t1.roleId in(?,?) or t2.expectJob = ? ) " 
	      + " and t1.companyId  in (?,?,?) "
	      + " union"
	      + " select tp.* from tb_person as tp where tp.cityId=? and tp.roleId in (?,?)"
	      + "  and tp.companyId  in (?,?,?)"
	      + " ) emp where id in (select id from tb_person where id=?) ";

	  public List<EmployeePerson> searchEmployeesByJob(JobDesc job,Integer personid) {
	    int domainId = job.getDomainID();
	    String jobName = job.getName();
	    long jobId = job.getRoleid();
	    int cityId = job.getCityId();
	   long expectJobId = job.getExpectJobId();
	    String companyName = job.getCompanyName();
	    int companyId = job.getCompanyId();
	    int companyId1 = job.getExpectcompanyid();
	    int companyId2 = job.getExpectcompanyid2();
	    int companyId3 = job.getExpectcompanyid3();
	    int expectDomainId1 = job.getMajorDomainId1();
	    int expectDomainId2 = job.getMajorDomainId2();
	    int expectDomainId3 = job.getMajorDomainId3();
	    
	    
	    return BaseDAO.queryForList(EmployeePerson.class, SEARCH_EMP_JOB, cityId, companyId, jobId, expectJobId, 
	                                expectDomainId1,expectDomainId2, expectDomainId3, cityId,  jobId, expectJobId, jobId,
	                               companyId1, companyId2, companyId3,
	                               //20170219 add
	                               cityId,  jobId, expectJobId, 
	                               companyId1, companyId2, companyId3,personid);
	  }
	  
	  //自动匹配---仅选择目前所在的细分领域
	  //候选人现在职位/期望职位 & 候选人细分领域的期望公司 &期望工作地点
	  //候选人现在职位/期望职位 & 候选人细分领域 & 期望工作地点
	  private static final String SEARCH_EMP_JOB1 = "select * from ("
		  		+ "select t1.* from tb_person as t1, " 
		  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
		   + " and ( t1.roleId in(?,?) or t2.expectJob = ? )  " 
		      + "and t2.domainId in (?) "
		      + " union "
		      + "select t1.* from tb_person as t1, " 
		  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
		   + " and  ( t1.roleId in(?,?) or t2.expectJob = ? ) " 
		      + " and t1.companyId  in (?) "
		      + " ) emp where id in (select id from tb_person where id=?) limit 0,10";
	  public List<EmployeePerson> searchEmployeesByJob1(JobDesc job,Integer personid) {
		    long jobId = job.getRoleid();
		    int cityId = job.getCityId();
		   long expectJobId = job.getExpectJobId();
		    int companyId1 = job.getExpectcompanyid();
		    int companyId2 = job.getExpectcompanyid2();
		    int companyId3 = job.getExpectcompanyid3();
		    int expectDomainId1 = job.getMajorDomainId1();
		    int expectDomainId2 = job.getMajorDomainId2();
		    int expectDomainId3 = job.getMajorDomainId3();
		    StringBuilder sb=new StringBuilder();
			if(expectDomainId1>0) {
				sb.append(expectDomainId1);
			}
			if(expectDomainId2>0) {
				sb.append(","+expectDomainId2);
			}
			if(expectDomainId3>0) {
				sb.append(","+expectDomainId3);
			}
			String ids=""+sb;
			StringBuilder sbcompany=new StringBuilder();
			if(companyId1>0) {
				sbcompany.append(companyId1);
			}
			if(companyId2>0) {
				sbcompany.append(","+companyId2);
			}
			if(companyId3>0) {
				sbcompany.append(","+companyId3);
			}
			String companys=""+sbcompany;
		    
		    return BaseDAO.queryForList(EmployeePerson.class, SEARCH_EMP_JOB1, cityId, jobId, expectJobId, jobId, 
		    							ids, cityId,  jobId, expectJobId, jobId,
		    							companys,personid);
		}
		  
	 //自动匹配--选择现领域及跨领域
	 //候选人现在职位/期望职位 & 候选人细分领域的期望公司 &期望工作地点
	 //候选人现在职位/期望职位 & 候选人细分领域 & 期望工作地点
	 //候选人现在职位/期望职位 & 非现在细分行业期望公司 &期望工作地点
	 //候选人现在职位/期望职位 & 期望行业（不含现在细分领域） & 期望工作地点
	  private static final String SEARCH_EMP_JOB2 = "select * from ("
		  		+ "select t1.* from tb_person as t1, " 
		  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
		   + " and t1.companyId <> ? and  ( t1.roleId in(?,?) or t2.expectJob = ? ) " 
		   + " union "
		      + "select t1.* from tb_person as t1, " 
		  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
		   + " and  ( t1.roleId in(?,?) or t2.expectJob = ? ) " 
		      + " and t2.domainId  in (?) "
		      + " union "
		      + "select t1.* from tb_person as t1, " 
		  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
		   + " and  ( t1.roleId in(?,?) or t2.expectJob = ? ) " 
		      + " and t1.companyId  in (?) "
		      + " union"
		      + " select tp.* from tb_person as tp where tp.cityId=? and tp.roleId in (?,?)"
		      + "  and tp.domainId=? "
		      + " ) emp where id in (select id from tb_person where id=?) ";
		  public List<EmployeePerson> searchEmployeesByJob2(JobDesc job,Integer personid) {
		    int domainId = job.getDomainID();
		    long jobId = job.getRoleid();
		    int cityId = job.getCityId();
		   long expectJobId = job.getExpectJobId();
		    int companyId = job.getCompanyId();
		    int companyId1 = job.getExpectcompanyid();
		    int companyId2 = job.getExpectcompanyid2();
		    int companyId3 = job.getExpectcompanyid3();
		    int expectDomainId1 = job.getMajorDomainId1();
		    int expectDomainId2 = job.getMajorDomainId2();
		    int expectDomainId3 = job.getMajorDomainId3();
		    StringBuilder sb=new StringBuilder();
			if(expectDomainId1>0) {
				sb.append(expectDomainId1);
			}
			if(expectDomainId2>0) {
				sb.append(","+expectDomainId2);
			}
			if(expectDomainId3>0) {
				sb.append(","+expectDomainId3);
			}
			String ids=""+sb;
			StringBuilder sbcompany=new StringBuilder();
			if(companyId1>0) {
				sbcompany.append(companyId1);
			}
			if(companyId2>0) {
				sbcompany.append(","+companyId2);
			}
			if(companyId3>0) {
				sbcompany.append(","+companyId3);
			}
			String companys=""+sbcompany;
		    return BaseDAO.queryForList(EmployeePerson.class, SEARCH_EMP_JOB2, cityId, companyId, jobId, expectJobId, jobId, 
		                                 cityId,  jobId, expectJobId, jobId,ids,
		                                 cityId,  jobId, expectJobId, jobId,companys,
		                                 cityId,  jobId, expectJobId, domainId,personid);
		  }

		  
		  //自动匹配---跨医疗/医药领域
		  //候选人现在职位/期望职位 & 非现在细分行业期望公司 &期望工作地点
		  //候选人现在职位/期望职位 & 期望行业（不含现在细分领域） & 期望工作地点
		  private static final String SEARCH_EMP_JOB3 = "select * from ("
			  		+ "select t1.* from tb_person as t1, " 
			  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
			   + " and ( t1.roleId in(?,?) or t2.expectJob = ? )  " 
			      + "and t1.companyId <> ? "
			      + " union "
			      + "select t1.* from tb_person as t1, " 
			  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
			   + " and  ( t1.roleId in(?,?) or t2.expectJob = ? ) " 
			      + " and t1.domainId =?"
			      + " ) emp where id in (select id from tb_person where id=?) ";
		  public List<EmployeePerson> searchEmployeesByJob3(JobDesc job,Integer personid) {
			   long jobId = job.getRoleid();
			   int cityId = job.getCityId();
			   long expectJobId = job.getExpectJobId();
			   int companyId = job.getCompanyId();
			    int expectDomainId2 = job.getMajorDomainId2();
			    int expectDomainId3 = job.getMajorDomainId3();
			    StringBuilder sb=new StringBuilder();
				if(expectDomainId2>0) {
					sb.append(expectDomainId2);
				}
				if(expectDomainId3>0) {
					sb.append(","+expectDomainId3);
				}
				String ids=""+sb;
			   return BaseDAO.queryForList(EmployeePerson.class, SEARCH_EMP_JOB3, cityId, jobId, expectJobId, jobId, 
			    		companyId, cityId,  jobId, expectJobId, jobId,ids,personid);
			}
		  
		  private final static String SQL_GETBYID = "select * from tb_employer where id = ?";
		public Employee getByEmpIdSimple(Integer valueOf) {
			// TODO Auto-generated method stub
			return BaseDAO.queryForObject(Employee.class, SQL_GETBYID, valueOf);
		}

//		protected static final String SQL_GET_EMPPERSON_UNIONID = 
//				"select b.*,p.id personId,p.name,p.phone,p.workage,p.companyId,p.companyName,p.domainId,p.domainName,"
//				+ "p.roleId,p.roleName,p.roleFullName,p.cityId,p.cityName,p.bestdomain "
//				+ " from userinfo a LEFT JOIN tb_employee b on a.openid=b.openid "
//				+ " left join tb_person p on p.openid=b.openid "
//				+ " where a.unionid=? order by empid desc LIMIT 0,1";
		protected static final String SQL_GET_EMPPERSON_UNIONID = 
		"select b.*,p.id personId,p.name,p.phone,p.workage,p.companyId,p.companyName,p.domainId,p.domainName,"
		+ "p.roleId,p.roleName,p.roleFullName,p.cityId,p.cityName,p.bestdomain "
		+ " from tb_employee b  "
		+ " left join tb_person p on p.openid=b.openid "
		+ " where p.openid=? order by empid desc LIMIT 0,1";
		public EmployeePerson getEmpPersonByUnionid(String unionid){
			return BaseDAO.queryForObject(EmployeePerson.class, SQL_GET_EMPPERSON_UNIONID, unionid);
		}
		
		public static final String SQL_SAVE="INSERT INTO `tb_employee` "
				+ "(`openid`, `personid`,  "
				+ "`expectCompany1`, `expectCompanyName1`, `expectCompany2`, "
				+ "`expectCompanyName2`, `expectCompany3`, `expectCompanyName3`, "
				+ "`phone`, `state`,  `expectJobName`, `expectJob`, "
				+ "   `comment`, "
				+ " `expiredTime`, `active`,"
				+ " `majorDomainId1`, `majorDomainId2`, `majorDomainId3`) "
				+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
		public int save(EmployeePerson emp) {
			long id = BaseDAO.executeInsertGenKey(SQL_SAVE, emp.getOpenid(),emp.getPersonId(),emp.getExpectCompany1(),
					emp.getExpectCompanyName1(),emp.getExpectCompany2(),emp.getExpectCompanyName2(),
					emp.getExpectCompany3(),emp.getExpectCompanyName3(),emp.getPhone(),"completed",emp.getExpectJobName(),
					emp.getExpectJob(),emp.getComment(),new Date(),1,emp.getMajorDomainId1(),
					emp.getMajorDomainId2(),emp.getMajorDomainId3());
			return (int)id;
		}
		public static final String SQL_UPDATE = "update tb_employee set expectCompany1=?,"
				+ "expectCompanyName1=?,expectCompany2=?,expectCompanyName2=?,expectCompany3=?,"
				+ "expectCompanyName3=?,expectJobName=?,expectJob=?,comment=?,"
				+ "expiredTime=?,majorDomainId1=?,majorDomainId2=?,majorDomainId3=? where empid =?";
		public int update(EmployeePerson emp) {
			return BaseDAO.execute(SQL_UPDATE, emp.getExpectCompany1(),emp.getExpectCompanyName1(),
					emp.getExpectCompany2(),emp.getExpectCompanyName2(),emp.getExpectCompany3(),
					emp.getExpectCompanyName3(),emp.getExpectJobName(),emp.getExpectJob(),
					emp.getComment(),new Date(),emp.getMajorDomainId1(),emp.getMajorDomainId2(),
					emp.getMajorDomainId3(),emp.getEmpid());
		}

}
